with base_bill_site_1 as (
select
     t.date_time
    ,t.taking_code     --揽收网点
    ,t.taking_name
    ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,t.taking_fran_code                  --揽收网点加盟商
    ,t.taking_fran_name                  --揽收网点加盟商code
    ,t.taking_agent_code                --'代理区编码',
    ,t.taking_agent_name                --'代理区名称,',
    ,t.taking_manage_code              --'管理大区名称',
    ,t.taking_manage_name              --'管理大区名称',
    ,t.taking_provider_id              --'省份ID',
    ,t.taking_provider_desc          --'省份',
    ,t.taking_city_id                    --'城市ID',
    ,t.taking_city_desc                  --'城市',
    ,t.taking_area_id                      --'区/县ID',
    ,t.taking_area_desc                  --'区/县',
    ,t.first_center_network_code --始发中心
    ,t.first_center_network_name
    ,t.end_center_network_code --末端中心
    ,t.end_center_network_name
    ,t.sign_network_code --签收网点
    ,t.sign_network_name  --签收网点
    ,t.sign_network_type
    ,t.sign_fran_code                 --签收网点加盟商
    ,t.sign_fran_name                 --签收网点加盟商code
    ,t.sign_agent_code              --'代理区编码',
    ,t.sign_agent_name               --'代理区名称,',
    ,t.sign_manage_code             --'管理大区名称',
    ,t.sign_manage_name             --'管理大区名称',
    ,t.sign_provider_id             --'省份ID',
    ,t.sign_provider_desc        --'省份',
    ,t.sign_city_id                        --'城市ID',
    ,t.sign_city_desc                 --'城市',
    ,t.sign_area_id                      --'区/县ID',
    ,t.sign_area_desc                     --'区/县',
--     ,count(t.waybill_no) as  front_number
--     ,sum(gh_route_num)*1.00/  count(t.waybill_no) front_plan_zz
--     ,sum(sj_route_num-lianxu_name_num ) *1.00 /  count(t.waybill_no)   front_actual_zz
--     ,sum(t.sj_agent_in_count)*1.00 /  count(t.waybill_no) as front_agent_in
--     ,sum(t.sj_agent_out_count)*1.00 /  count(t.waybill_no) as front_agent_out
--     ,sum(is_reback_flg) as front_reback
--     ,sum(case when is_reback_flg=1 then sj_route_num-lianxu_name_num else 0 end )*1.00/ sum(is_reback_flg) as front_reback_zz
--     ,sum(is_rational_flg) as front_rational
--     ,sum(case when is_rational_flg=1 then sj_route_num-lianxu_name_num else 0 end )*1.00/ sum(is_rational_flg) as front_rational_zz
--     ,sum(is_proble_flg) as front_proble
--     ,sum(case when is_proble_flg=1 then sj_route_num-lianxu_name_num else 0 end )*1.00/ sum(is_proble_flg) as front_proble_zz

--     ,count(t.waybill_no) as  front_number
--     ,sum(gh_route_num) front_plan_zz
--     ,sum(sj_route_num)    front_actual_zz
    ,sum(case when is_reback_flg <>1 then 1 else 0 end ) as  front_number
    ,sum(case when is_reback_flg <>1 then gh_route_num else 0 end ) front_plan_zz
    ,sum(case when is_reback_flg <>1 then sj_route_num else 0 end  )    front_actual_zz

    ,sum(t.sj_agent_in_count) as front_agent_in
    ,sum(t.sj_agent_out_count) as front_agent_out
    ,sum(is_reback_flg) as front_reback
    ,sum(case when is_reback_flg=1 then sj_route_num else 0 end ) as front_reback_zz
    ,sum(is_rational_flg) as front_rational
    ,sum(case when is_rational_flg=1 then sj_route_num else 0 end ) front_rational_zz
    ,sum(is_proble_flg) as front_proble
    ,sum(case when is_proble_flg=1 then sj_route_num else 0 end ) as front_proble_zz
    ,sum(is_wrong_flg) as front_is_wrong
    ,sum(case when is_wrong_flg=1 then sj_route_num else 0 end ) as front_is_wrong_zz
    ,sum(is_bak_route) as front_bak_route
    ,sum(case when is_bak_route=1 then bak_route_num else 0 end ) as front_bak_route_zz
    ,t.first_nodal_network_code
    ,t.first_nodal_network_name
    ,t.end_nodal_network_code
    ,t.end_nodal_network_name
    ,t.gh_first_center_name
    ,t.gh_first_center_code
    ,t.gh_end_center_name
    ,t.gh_end_center_code
    ,sum(t.is_route_error_flg) as  route_error_number
    ,sum(case when t.is_route_error_flg=1 then sj_route_num else 0 end  )    route_error_actual_zz
    ,sum(t.is_two_must_flg) as  two_must_number
    ,sum(case when t.is_two_must_flg=1 then sj_route_num else 0 end )    two_must_actual_zz

from jms_dm.dm_all_transfer_count_deatil_dt t
where   t.dt='{{ execution_date |  cst_ds }}'   and  t.taking_code is not null and t.sign_network_code  is not null

group by
     t.date_time
    ,t.taking_code     --揽收网点
    ,t.taking_name
    ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,t.taking_fran_code                  --揽收网点加盟商
    ,t.taking_fran_name                  --揽收网点加盟商code
    ,t.taking_agent_code                --'代理区编码',
    ,t.taking_agent_name                --'代理区名称,',
    ,t.taking_manage_code              --'管理大区名称',
    ,t.taking_manage_name              --'管理大区名称',
    ,t.taking_provider_id              --'省份ID',
    ,t.taking_provider_desc          --'省份',
    ,t.taking_city_id                    --'城市ID',
    ,t.taking_city_desc                  --'城市',
    ,t.taking_area_id                      --'区/县ID',
    ,t.taking_area_desc                  --'区/县',
    ,t.first_center_network_code --始发中心
    ,t.first_center_network_name
    ,t.end_center_network_code --末端中心
    ,t.end_center_network_name
    ,t.sign_network_code --签收网点
    ,t.sign_network_name  --签收网点
    ,t.sign_network_type
    ,t.sign_fran_code                 --签收网点加盟商
    ,t.sign_fran_name                 --签收网点加盟商code
    ,t.sign_agent_code              --'代理区编码',
    ,t.sign_agent_name               --'代理区名称,',
    ,t.sign_manage_code             --'管理大区名称',
    ,t.sign_manage_name             --'管理大区名称',
    ,t.sign_provider_id             --'省份ID',
    ,t.sign_provider_desc        --'省份',
    ,t.sign_city_id                        --'城市ID',
    ,t.sign_city_desc                 --'城市',
    ,t.sign_area_id                      --'区/县ID',
    ,t.sign_area_desc                     --'区/县',
    ,t.first_nodal_network_code
    ,t.first_nodal_network_name
    ,t.end_nodal_network_code
    ,t.end_nodal_network_name
    ,t.gh_first_center_name
    ,t.gh_first_center_code
    ,t.gh_end_center_name
    ,t.gh_end_center_code
),
base_bill_site_2 as (
        select
             t.date_time
            ,t.taking_code     --揽收网点
            ,t.taking_name
            ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
            ,t.taking_fran_code                  --揽收网点加盟商
            ,t.taking_fran_name                  --揽收网点加盟商code
            ,t.taking_agent_code                --'代理区编码',
            ,t.taking_agent_name                --'代理区名称,',
            ,t.taking_manage_code              --'管理大区名称',
            ,t.taking_manage_name              --'管理大区名称',
            ,t.taking_provider_id              --'省份ID',
            ,t.taking_provider_desc          --'省份',
            ,t.taking_city_id                    --'城市ID',
            ,t.taking_city_desc                  --'城市',
            ,t.taking_area_id                      --'区/县ID',
            ,t.taking_area_desc                  --'区/县',
            ,t.sign_network_code --签收网点
            ,t.sign_network_name  --签收网点
            ,t.sign_network_type
            ,t.sign_fran_code                 --签收网点加盟商
            ,t.sign_fran_name                 --签收网点加盟商code
            ,t.sign_agent_code              --'代理区编码',
            ,t.sign_agent_name               --'代理区名称,',
            ,t.sign_manage_code             --'管理大区名称',
            ,t.sign_manage_name             --'管理大区名称',
            ,t.sign_provider_id             --'省份ID',
            ,t.sign_provider_desc        --'省份',
            ,t.sign_city_id                        --'城市ID',
            ,t.sign_city_desc                 --'城市',
            ,t.sign_area_id                      --'区/县ID',
            ,t.sign_area_desc                     --'区/县',
            ,t.first_center_network_code --始发中心
            ,t.first_center_network_name
            ,t.end_center_network_code --末端中心
            ,t.end_center_network_name
            ,sum(front_number)   front_number
            ,sum(front_plan_zz)   front_plan_zz
            ,sum(front_actual_zz)   front_actual_zz
            ,sum(front_agent_in)   front_agent_in
            ,sum(front_agent_out)   front_agent_out
            ,sum(front_reback)   front_reback
            ,sum(front_reback_zz)   front_reback_zz
            ,sum(front_rational)   front_rational
            ,sum(front_rational_zz)   front_rational_zz
            ,sum(front_proble)   front_proble
            ,sum(front_proble_zz)   front_proble_zz
            ,sum(back_number)   back_number
            ,sum(back_plan_zz)   back_plan_zz
            ,sum(back_actual_zz)   back_actual_zz
            ,sum(back_agent_in)   back_agent_in
            ,sum(back_agent_out)   back_agent_out
            ,sum(back_reback)   back_reback
            ,sum(back_reback_zz)   back_reback_zz
            ,sum(back_rational)   back_rational
            ,sum(back_rational_zz)   back_rational_zz
            ,sum(back_proble)   back_proble
            ,sum(back_proble_zz)   back_proble_zz
            ,sum(front_is_wrong) front_is_wrong
            ,sum(front_is_wrong_zz) front_is_wrong_zz
            ,sum(back_is_wrong) back_is_wrong
            ,sum(back_is_wrong_zz) back_is_wrong_zz

            ,sum(front_bak_route) front_bak_route
            ,sum(front_bak_route_zz) front_bak_route_zz
            ,sum(back_bak_route)    back_bak_route
            ,sum(back_bak_route_zz) back_bak_route_zz
            ,first_nodal_network_code
            ,first_nodal_network_name
            ,end_nodal_network_code
            ,end_nodal_network_name
            ,t.gh_first_center_name
            ,t.gh_first_center_code
            ,t.gh_end_center_name
            ,t.gh_end_center_code
            ,sum(t.route_error_number) route_error_number
            ,sum(t.route_error_actual_zz) route_error_actual_zz
            ,sum(t.two_must_number) two_must_number
            ,sum(t.two_must_actual_zz) two_must_actual_zz
            ,sum(t.back_route_error_number) back_route_error_number
            ,sum(t.back_route_error_actual_zz) back_route_error_actual_zz
            ,sum(t.back_two_must_number) back_two_must_number
            ,sum(t.back_two_must_actual_zz) back_two_must_actual_zz
        from (
                select
                     t.date_time
                    ,t.taking_code     --揽收网点
                    ,t.taking_name
                    ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
                    ,t.taking_fran_code                  --揽收网点加盟商
                    ,t.taking_fran_name                  --揽收网点加盟商code
                    ,t.taking_agent_code                --'代理区编码',
                    ,t.taking_agent_name                --'代理区名称,',
                    ,t.taking_manage_code              --'管理大区名称',
                    ,t.taking_manage_name              --'管理大区名称',
                    ,t.taking_provider_id              --'省份ID',
                    ,t.taking_provider_desc          --'省份',
                    ,t.taking_city_id                    --'城市ID',
                    ,t.taking_city_desc                  --'城市',
                    ,t.taking_area_id                      --'区/县ID',
                    ,t.taking_area_desc                  --'区/县',
                    ,t.first_center_network_code --始发中心
                    ,t.first_center_network_name
                    ,t.end_center_network_code --末端中心
                    ,t.end_center_network_name
                    ,t.sign_network_code --签收网点
                    ,t.sign_network_name  --签收网点
                    ,t.sign_network_type
                    ,t.sign_fran_code                 --签收网点加盟商
                    ,t.sign_fran_name                 --签收网点加盟商code
                    ,t.sign_agent_code              --'代理区编码',
                    ,t.sign_agent_name               --'代理区名称,',
                    ,t.sign_manage_code             --'管理大区名称',
                    ,t.sign_manage_name             --'管理大区名称',
                    ,t.sign_provider_id             --'省份ID',
                    ,t.sign_provider_desc        --'省份',
                    ,t.sign_city_id                        --'城市ID',
                    ,t.sign_city_desc                 --'城市',
                    ,t.sign_area_id                      --'区/县ID',
                    ,t.sign_area_desc                     --'区/县',
                    ,t.front_number
                    ,t.front_plan_zz
                    ,t.front_actual_zz
                    ,t.front_agent_in
                    ,t.front_agent_out
                    ,t.front_reback
                    ,t.front_reback_zz
                    ,t.front_rational
                    ,t.front_rational_zz
                    ,t.front_proble
                    ,t.front_proble_zz
                    ,t.front_is_wrong
                    ,t.front_is_wrong_zz
                    ,t.front_bak_route
                    ,t.front_bak_route_zz
                    ,0 as back_number
                    ,0 as back_plan_zz
                    ,0 as back_actual_zz
                    ,0 as back_agent_in
                    ,0 as back_agent_out
                    ,0 as back_reback
                    ,0 as back_reback_zz
                    ,0 as back_rational
                    ,0 as back_rational_zz
                    ,0 as back_proble
                    ,0 as back_proble_zz
                    ,0 as back_is_wrong
                    ,0 as back_is_wrong_zz
                    ,0 as back_bak_route
                    ,0 as back_bak_route_zz
                    ,first_nodal_network_code
                    ,first_nodal_network_name
                    ,end_nodal_network_code
                    ,end_nodal_network_name
                    ,t.gh_first_center_name
                    ,t.gh_first_center_code
                    ,t.gh_end_center_name
                    ,t.gh_end_center_code
                    ,t.route_error_number
                    ,t.route_error_actual_zz
                    ,t.two_must_number
                    ,t.two_must_actual_zz
                    ,0 as back_route_error_number
                    ,0 as back_route_error_actual_zz
                    ,0 as back_two_must_number
                    ,0 as back_two_must_actual_zz
                from base_bill_site_1 t
                union all
                select
                     t.date_time
                    ,t.sign_network_code as    taking_code
                    ,t.sign_network_name  as    taking_name
                    ,t.sign_network_type as    taking_network_type
                    ,t.sign_fran_code                 as    taking_fran_code
                    ,t.sign_fran_name                 as    taking_fran_name
                    ,t.sign_agent_code              as    taking_agent_code
                    ,t.sign_agent_name               as    taking_agent_name
                    ,t.sign_manage_code             as    taking_manage_code
                    ,t.sign_manage_name             as    taking_manage_name
                    ,t.sign_provider_id             as    taking_provider_id
                    ,t.sign_provider_desc        as    taking_provider_desc
                    ,t.sign_city_id                        as    taking_city_id
                    ,t.sign_city_desc                 as    taking_city_desc
                    ,t.sign_area_id                      as    taking_area_id
                    ,t.sign_area_desc                     as    taking_area_desc
                    ,t.end_center_network_code as    first_center_network_code
                    ,t.end_center_network_name as    first_center_network_name
                    ,t.first_center_network_code as    end_center_network_code
                    ,t.first_center_network_name as    end_center_network_name
                    ,t.taking_code                  as    sign_network_code
                    ,t.taking_name as    sign_network_name
                    ,t.taking_network_type            as    sign_network_type
                    ,t.taking_fran_code                  as    sign_fran_code
                    ,t.taking_fran_name                  as    sign_fran_name
                    ,t.taking_agent_code                as    sign_agent_code
                    ,t.taking_agent_name                as    sign_agent_name
                    ,t.taking_manage_code              as    sign_manage_code
                    ,t.taking_manage_name              as    sign_manage_name
                    ,t.taking_provider_id              as    sign_provider_id
                    ,t.taking_provider_desc          as    sign_provider_desc
                    ,t.taking_city_id                    as    sign_city_id
                    ,t.taking_city_desc                  as    sign_city_desc
                    ,t.taking_area_id                      as    sign_area_id
                    ,t.taking_area_desc                  as    sign_area_desc
                    ,0 as front_number
                    ,0 as front_plan_zz
                    ,0 as front_actual_zz
                    ,0 as front_agent_in
                    ,0 as front_agent_out
                    ,0 as front_reback
                    ,0 as front_reback_zz
                    ,0 as front_rational
                    ,0 as front_rational_zz
                    ,0 as front_proble
                    ,0 as front_proble_zz
                    ,0 as front_is_wrong
                    ,0 as front_is_wrong_zz
                    ,0 as front_bak_route
                    ,0 as front_bak_route_zz
                    ,t.front_number as back_number
                    ,t.front_plan_zz as back_plan_zz
                    ,t.front_actual_zz as back_actual_zz
                    ,t.front_agent_in as back_agent_in
                    ,t.front_agent_out as back_agent_out
                    ,t.front_reback as back_reback
                    ,t.front_reback_zz as back_reback_zz
                    ,t.front_rational as back_rational
                    ,t.front_rational_zz as back_rational_zz
                    ,t.front_proble as back_proble
                    ,t.front_proble_zz as back_proble_zz
                    ,t.front_is_wrong as back_is_wrong_zz
                    ,t.front_is_wrong_zz  as back_is_wrong_zz
                    ,t.front_bak_route    as back_bak_route
                    ,t.front_bak_route_zz as back_bak_route_zz
                    ,first_nodal_network_code
                    ,first_nodal_network_name
                    ,end_nodal_network_code
                    ,end_nodal_network_name
                    ,t.gh_first_center_name
                    ,t.gh_first_center_code
                    ,t.gh_end_center_name
                    ,t.gh_end_center_code
                    ,0 as route_error_number
                    ,0 as route_error_actual_zz
                    ,0 as two_must_number
                    ,0 as two_must_actual_zz
                    ,t.route_error_number as back_route_error_number
                    ,t.route_error_actual_zz as back_route_error_actual_zz
                    ,t.two_must_number as back_two_must_number
                    ,t.two_must_actual_zz as back_two_must_actual_zz
                    from base_bill_site_1 t
            ) t
            group by
             t.date_time
            ,t.taking_code     --揽收网点
            ,t.taking_name
            ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
            ,t.taking_fran_code                  --揽收网点加盟商
            ,t.taking_fran_name                  --揽收网点加盟商code
            ,t.taking_agent_code                --'代理区编码',
            ,t.taking_agent_name                --'代理区名称,',
            ,t.taking_manage_code              --'管理大区名称',
            ,t.taking_manage_name              --'管理大区名称',
            ,t.taking_provider_id              --'省份ID',
            ,t.taking_provider_desc          --'省份',
            ,t.taking_city_id                    --'城市ID',
            ,t.taking_city_desc                  --'城市',
            ,t.taking_area_id                      --'区/县ID',
            ,t.taking_area_desc                  --'区/县',
            ,t.sign_network_code --签收网点
            ,t.sign_network_name  --签收网点
            ,t.sign_network_type
            ,t.sign_fran_code                 --签收网点加盟商
            ,t.sign_fran_name                 --签收网点加盟商code
            ,t.sign_agent_code              --'代理区编码',
            ,t.sign_agent_name               --'代理区名称,',
            ,t.sign_manage_code             --'管理大区名称',
            ,t.sign_manage_name             --'管理大区名称',
            ,t.sign_provider_id             --'省份ID',
            ,t.sign_provider_desc        --'省份',
            ,t.sign_city_id                        --'城市ID',
            ,t.sign_city_desc                 --'城市',
            ,t.sign_area_id                      --'区/县ID',
            ,t.sign_area_desc                     --'区/县',
            ,t.first_center_network_code --始发中心
            ,t.first_center_network_name
            ,t.end_center_network_code --末端中心
            ,t.end_center_network_name
            ,first_nodal_network_code
            ,first_nodal_network_name
            ,end_nodal_network_code
            ,end_nodal_network_name
            ,t.gh_first_center_name
            ,t.gh_first_center_code
            ,t.gh_end_center_name
            ,t.gh_end_center_code
)


insert overwrite table jms_dm.dm_all_transfer_count_site_dt partition (dt)
select
     t.date_time
    ,t.taking_code     --揽收网点
    ,t.taking_name
    ,t.taking_network_type            --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,t.taking_fran_code                  --揽收网点加盟商
    ,t.taking_fran_name                  --揽收网点加盟商code
    ,t.taking_agent_code                --'代理区编码',
    ,t.taking_agent_name                --'代理区名称,',
    ,t.taking_manage_code              --'管理大区名称',
    ,t.taking_manage_name              --'管理大区名称',
    ,t.taking_provider_id              --'省份ID',
    ,t.taking_provider_desc          --'省份',
    ,t.taking_city_id                    --'城市ID',
    ,t.taking_city_desc                  --'城市',
    ,t.taking_area_id                      --'区/县ID',
    ,t.taking_area_desc                  --'区/县',
    ,t.sign_network_code --签收网点
    ,t.sign_network_name  --签收网点
    ,t.sign_network_type
    ,t.sign_fran_code                 --签收网点加盟商
    ,t.sign_fran_name                 --签收网点加盟商code
    ,t.sign_agent_code              --'代理区编码',
    ,t.sign_agent_name               --'代理区名称,',
    ,t.sign_manage_code             --'管理大区名称',
    ,t.sign_manage_name             --'管理大区名称',
    ,t.sign_provider_id             --'省份ID',
    ,t.sign_provider_desc        --'省份',
    ,t.sign_city_id                        --'城市ID',
    ,t.sign_city_desc                 --'城市',
    ,t.sign_area_id                      --'区/县ID',
    ,t.sign_area_desc                     --'区/县',
    ,t.first_center_network_code --始发中心
    ,t.first_center_network_name
    ,t.end_center_network_code --末端中心
    ,t.end_center_network_name
    ,nvl(t.front_number,0) as front_number
    ,nvl(t.front_plan_zz,0) as front_plan_zz
    ,nvl(t.front_actual_zz,0) as front_actual_zz
    ,nvl(t.front_agent_in,0) as front_agent_in
    ,nvl(t.front_agent_out,0) as front_agent_out
    ,nvl(t.front_reback,0) as front_reback
    ,nvl(t.front_reback_zz,0) as front_reback_zz
    ,nvl(t.front_rational,0) as front_rational
    ,nvl(t.front_rational_zz,0) as front_rational_zz
    ,nvl(t.front_proble,0) as front_proble
    ,nvl(t.front_proble_zz,0) as front_proble_zz
    ,nvl(t.back_number,0) as back_number
    ,nvl(t.back_plan_zz,0) as back_plan_zz
    ,nvl(t.back_actual_zz,0) as back_actual_zz
    ,nvl(t.back_agent_in,0) as back_agent_in
    ,nvl(t.back_agent_out,0) as back_agent_out
    ,nvl(t.back_reback,0) as back_reback
    ,nvl(t.back_reback_zz,0) as back_reback_zz
    ,nvl(t.back_rational,0) as back_rational
    ,nvl(t.back_rational_zz,0) as back_rational_zz
    ,nvl(t.back_proble,0) as back_proble
    ,nvl(t.back_proble_zz,0) as back_proble_zz
    ,nvl(t.front_is_wrong,0) front_is_wrong
    ,nvl(t.front_is_wrong_zz,0) front_is_wrong_zz
    ,nvl(t.back_is_wrong,0) back_is_wrong
    ,nvl(t.back_is_wrong_zz,0) back_is_wrong_zz

    ,nvl(t.front_bak_route,0) front_bak_route
    ,nvl(t.front_bak_route_zz,0) front_bak_route_zz
    ,nvl(t.back_bak_route,0)    back_bak_route
    ,nvl(t.back_bak_route_zz,0) back_bak_route_zz
    ,first_nodal_network_code
    ,first_nodal_network_name
    ,end_nodal_network_code
    ,end_nodal_network_name
    ,t.gh_first_center_name
    ,t.gh_first_center_code
    ,t.gh_end_center_name
    ,t.gh_end_center_code
    ,nvl(t.route_error_number,0) route_error_number
    ,nvl(t.route_error_actual_zz,0) route_error_actual_zz
    ,nvl(t.two_must_number,0) two_must_number
    ,nvl(t.two_must_actual_zz,0) two_must_actual_zz
    ,nvl(t.back_route_error_number,0) back_route_error_number
    ,nvl(t.back_route_error_actual_zz,0) back_route_error_actual_zz
    ,nvl(t.back_two_must_number,0) back_two_must_number
    ,nvl(t.back_two_must_actual_zz,0) back_two_must_actual_zz
    ,t.date_time as dt
from base_bill_site_2 t
 distribute by pmod(hash(rand()), 72)
;

